﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Task_Network_Center.General;
using MySql.Data.MySqlClient;
using System.Windows;
using System.Data;
using System.Windows.Controls;

namespace Task_Network_Center.DataBaseTable
{
    public class EmployeTable
    {
        MySqlConnection conn  = null;
        MySqlConnection conn2 = null;

        public object[,] ListEmployeToStringArray()
        {
             object[,] result = new object[4,getEmployeRowCount()];
            int i = 0;
            try
            {
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();
                conn.Open();
                String query = "SELECT id_employes, initial, courriel FROM tableemployees ORDER BY initial";
                MySqlCommand cmd = new MySqlCommand(query,conn);
                MySqlDataReader dr = cmd.ExecuteReader();
                
                while(dr.Read())
                {
                    result[0,i] = dr.GetInt32(0);
                    result[1,i] = dr.GetString(1);
                    if (!dr.IsDBNull(2))
                    {
                        result[2, i] = dr.GetString(2); 
                    }
                    else
                    {
                        result[2, i] = "";
                    }
                    result[3,i] = i;
                    i++;
                }
                dr.Close();
                conn.Close();
                conn = null;
                
                return result;
            }
            catch(Exception e)
            {
                MessageBox.Show("Erreur TableEmploye - ListEmployeToStringArray: " + e);
                return null;
            }
        }
        

        private Int64 getEmployeRowCount()
        {

            try
            {
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();

                String query = "SELECT COUNT(*) FROM tableemployees";
                MySqlCommand cmd = new MySqlCommand(query, conn);
                conn.Open();

                Int64 result = (Int64)cmd.ExecuteScalar();

                conn.Close();
                conn = null;

                return result;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur TableEmploye - getEmployeRowCount: " + e);
                return -1;
            }
        }

        public DataTable QueryTableReadable(string query, object[,] departementArray, object[,] equipeArray)
        {
            DataTable result = new DataTable();

            result.Columns.Add("id_employes");
            result.Columns.Add("nom_complet");
            result.Columns.Add("equipe");
            result.Columns.Add("initial");
            result.Columns.Add("departement");
            result.Columns.Add("courriel");
            result.Columns.Add("notification");

           
            string nomComplet = "", initial = "", courriel = "",id = "", equipe = "", departement = "";
            bool notification = false;

            try
            {
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();
                conn.Open();
                //String query = "SELECT * FROM tableemployees ORDER BY initial";
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    if (!dr.IsDBNull(0))
                    {
                        id = dr.GetInt32(0).ToString();
                    }
                    else
                    {
                        id = "-1";
                    }
                    
                    if (!dr.IsDBNull(1))
                    {
                        nomComplet = dr.GetString(1);
                    }
                    else
                    {
                        nomComplet = "";
                    }

                    int cEquipe = dr.GetInt32(2);
                    if (Convert.ToInt32(dr.GetInt32(2)) != -1)
                    {
                        for (int i = 0; i < equipeArray.GetLength(1); i++)
                        {
                            if (cEquipe == Convert.ToInt32(equipeArray[0,i]))
                            {
                                equipe = equipeArray[1, i].ToString();
                            }
                        }
                    }
                    else
                    {
                        equipe = "";
                    }
                    if (!dr.IsDBNull(3))
                    {
                        initial = dr.GetString(3);
                    }
                    else
                    {
                        initial = "";
                    }

                    int cDepartement = dr.GetInt32(4);
                    if (Convert.ToInt32(dr.GetInt32(4)) != -1)
                    {
                        for (int i = 0; i < departementArray.GetLength(1); i++)
                        {
                            if (cDepartement == Convert.ToInt32(departementArray[0, i]))
                            {
                                departement = departementArray[1, i].ToString();
                            }
                        }
                    }
                    else
                    {
                        departement = "";
                    }

                    if (!dr.IsDBNull(5))
                    {
                        courriel = dr.GetString(5);
                    }
                    else
                    {
                        courriel = "";
                    }

                    if (!dr.IsDBNull(6))
                    {
                        notification = dr.GetBoolean(6);
                    }
                    else
                    {
                        notification = false;
                    }

                    result.Rows.Add(new Object[] {id,nomComplet,equipe,initial,departement,courriel,notification});
                }
                dr.Close();
                conn.Close();
                conn = null;

                return result;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur TableEmploye - QueryTableReadable: " + e);
                return null;
            }

        }



        public int InserRow(TextBox nom_complet, ComboBox equipe, TextBox initial, ComboBox departement, TextBox courriel, object[,] equipeArray, object[,] departementArray)
        {
            String query = "INSERT INTO tableemployees (id_employes,nom_complet,equipe,initial,departement,courriel) VALUE (@p1,@p2,@p3,@p4,@p5,@p6)";

            int id = employeID();

            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddWithValue("p1", id);

                cmd.Parameters.AddWithValue("p2", nom_complet.Text);

                if (equipe.SelectedIndex != -1)
                {
                    for (int i = 0; i < equipeArray.GetLength(1); i++)
                    {
                        if (equipe.SelectedValue.ToString() == equipeArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p3", equipeArray[0, i]);
                        }
                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p3", -1);
                }

                cmd.Parameters.AddWithValue("p4", initial.Text);

                if (departement.SelectedIndex != -1)
                {
                    for (int i = 0; i < departementArray.GetLength(1); i++)
                    {
                        if (departement.SelectedValue.ToString() == departementArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p5", departementArray[0, i]);
                        }
                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p5", -1);
                }
                cmd.Parameters.AddWithValue("p6", courriel.Text);

                cmd.ExecuteNonQuery();
                conn.Close();
                return id;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur DepartementTable - InsertRow: " + e);
                return -1;
            }

        }



        public bool UpdateRow(int id, TextBox nom_complet, ComboBox equipe, TextBox initial, ComboBox departement, TextBox courriel,object[,] equipeArray, object[,] departementArray)
        {
            String query = "UPDATE tableemployees SET nom_complet = @p1, equipe = @p2, initial = @p3, departement = @p4, courriel = @p5 WHERE id_employes = @p6";
            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddWithValue("p6", id);
                cmd.Parameters.AddWithValue("p1", nom_complet.Text);

                if (equipe.SelectedIndex != -1)
                {
                    for (int i = 0; i < equipeArray.GetLength(1); i++)
                    {
                        if (equipe.SelectedValue.ToString() == equipeArray[1,i].ToString())
                        {
                           cmd.Parameters.AddWithValue("p2", equipeArray[0,i]); 
                        }
                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p2", -1); 
                }
                
                cmd.Parameters.AddWithValue("p3", initial.Text);

                if (departement.SelectedIndex != -1)
                {
                    for (int i = 0; i < departementArray.GetLength(1); i++)
                    {
                        if (departement.SelectedValue.ToString() == departementArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p4", departementArray[0, i]);
                        }
                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p4", -1);
                }

                cmd.Parameters.AddWithValue("p5", courriel.Text);

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;

            }
            catch (Exception e)
            {

                MessageBox.Show("Erreur EmployeTable - UpdateRow: " + e);
                return false;
            }

        }



        private int employeID()
        {

            conn2 = GCGGeneralLib.DataBaseConnection.DbConnection();
            String query = "SELECT LAST_INSERT_ID() FROM tableemployees";
            MySqlCommand cmd = conn2.CreateCommand();
            cmd.CommandText = query;
            try
            {

                conn2.Open();
                cmd.ExecuteScalar();
                int count = Convert.ToInt32(cmd.LastInsertedId);
                conn2.Close();
                conn2 = null;
                return count + 1;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur EmployeTable - employeID: " + e);
                return -1;
            }

        }
    }
}
